jsでシリアル値を日付に変換する方法と、注意点

#Microsoft Excel

#JavaScript

北川慶太

2022.10.25

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは。データアナリティクス事業本部サービスソリューション部の北川です。

今回は、シリアル値を日付に変換する方法について、注意点も踏まえて記述していきます。

シリアル値とは

シリアル値は、1900年1月1日を1として、何日経過したかを示す数値です。Excelにおいて、日時を計算処理するために使用されます。

日付システム

1900日付システムと、1904日付システムという2つの日付システムがサポートされており、現在は1900日付システムを使用することがほとんどです。2つの日付システムの差分は1,462日になります。

シリアル値、日付システムに関しては、こちらのエントリに詳しく載っています。

シリアル値を日付に変換する

では、実際にシリアル値を日付に変換していきます。

1900年から1970年までの経過日数を求め、その変数とシリアル値の差分をミリ秒換算し、dayjsで日付変換を行います。

import dayjs from 'dayjs'

const ONEDAY = 24 * 60 * 60 * 1000 //1日をミリ秒に変換(JSにおける1日を表す)

// UNIX時間は1970年からの経過m秒なので、1900年から1970年までの経過日数を求める。
// 17は閏年の数。1900年に存在しない閏年を足す。
// シリアル値が1900/1/0という存在しない日付分を足す
const DATES_DIFF = 70 * 365 + 17 + 1 + 1

// シリアル値→UNIX時間
const changeJSDate = (serialNumber: number): number => {
  return (serialNumber - DATES_DIFF) * ONEDAY
}

export const numDate = (serialNumber: number): dayjs.Dayjs => {
  const JS_DATE = changeJSDate(serialNumber)

  return dayjs(JS_DATE)
}

存在しない閏年

ここで注意するのは1900年に存在しない閏年を、Excelでは計算している点になります。

1900年には本来存在しない閏年と、1900/1/0という存在しない日付から始まる点を考慮して、変数を定義します。

また、存在しない閏年のために、変数DATES_DIFFに１を足しているので、1900年の閏年前の日付は逆に１を余分に引いていることになります。

公式サイトを例に、シリアル値35981の時の日付もログを表示します。

  console.log(
    numDate(1).format('YYYY年MM月DD日'), // 本来は、1900年1月1日になる
    numDate(31).format('YYYY年MM月DD日'), // 本来は、1月31日
    numDate(59).format('YYYY年MM月DD日'), // 本来は、2月28日

    numDate(35981).format('YYYY年MM月DD日')　// 1998年7月５日
  )

1900年、閏年前のシリアル値には、一日ずれが生じています。

上記を踏まえ、現在のコードでは、シリアル値が60の場合に2月28日、61では3月1日になります。先ほど記述した通り、シリアル値には1900年2月29日が存在する想定なので、61を仮の2月29日になるようにコードを変更します。

import dayjs from 'dayjs'

const ONEDAY = 24 * 60 * 60 * 1000 //1日をミリ秒に変換(JSにおける1日を表す)
const DUMMY_LEAP_DAY_SERIAL_VAL = 61

// UNIX時間は1970年からの経過m秒なので、1900年から1970年までの経過日数を求める。
// 17は閏年の数。1900年に存在しない閏年を足す。
// シリアル値が「1900/1/0」という存在しない日付分を足す
const DATES_OFFSET = 70 * 365 + 17 + 1 + 1

const changeJSDate = (serialNumber: number): number => {
  // シリアル値→UNIX時間
  return (serialNumber - DATES_OFFSET) * ONEDAY
}

export const numDate = (serialNumber: number): dayjs.Dayjs => {
  const JS_DATE = changeJSDate(serialNumber)
  if (DUMMY_LEAP_DAY_SERIAL_VAL <= serialNumber) {
    return dayjs(JS_DATE)
  }
  return dayjs(JS_DATE + ONEDAY)
}

再度ログを見てみます。

  console.log(
    numDate(1).format('YYYY年MM月DD日'), // 本来は、1900年1月1日になる
    numDate(31).format('YYYY年MM月DD日'), // 1月31日
    numDate(59).format('YYYY年MM月DD日'), // 2月28日
    numDate(60).format('YYYY年MM月DD日'),
    numDate(61).format('YYYY年MM月DD日'),
    numDate(62).format('YYYY年MM月DD日'),
    numDate(35981).format('YYYY年MM月DD日')
  )

６０、６１がともに3月1日となり、重複が発生しています。これはdayjsでは、1900年2月29日が存在しないので、想定通りの結果と言えます。以上で、シリアル値を日付に変換することができました。

テストしてみた

余談ですが、シリアル値の変換が意図した通りに動いているかテストしてみました(成功しました)。

import dayjs from 'dayjs'
import { numDate } from './date'

const serialArray = [
1,
1.000011574074074,
1.0000231481481481,
1.0000347222222221,
1.0000462962962964,
        ...
1.9999652777777777,
1.9999768518518519,
1.9999884259259257
]

const array = [...Array(86400)].map((_, i) => i)
const dateArray = array.map((value) => {
  const d = dayjs('1900-01-01T00:00:00')

  return `${d.add(value, 's').format('YYYY-MM-DDTHH:mm:ss')}Z`
})

describe('numDate', () => {
  test('シリアル値の日付変換', async () => {

    serialArray.map((value, i) => {
      expect(numDate(value)).toEqual(dayjs(dateArray[i]))
    })
  })
})